alter table Promotion
add DateCompleted datetime null
go

/*-- Set date commit if needed
update Promotion
set DateCompleted = vw.DateCompleted 
from (select PromotionId, dateadd(DAY, 1, MAX(DatePush)) as DateCompleted 
		from PromotionPush 
		group by PromotionId) as vw
where Promotion.PromotionId = vw.PromotionId
	and Promotion.StatusCode = 'COM'
go	*/

ALTER PROCEDURE [dbo].[spAPI_Promotion_Push]
-- Use in other SP!!!
-- Not for call directly
(
	@UserId INT,
	@Latitude FLOAT,
	@Longitude FLOAT,
	@LocalTime DATETIME = NULL,
	
	@Result XML OUT
)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	declare @promotionIdCurr int
	
	CREATE TABLE #tmpPromotions
	(
		PromotionId INT,
		RatePerPush MONEY,
		Lat FLOAT,
		Lon FLOAT,
		WillStop bit default(0)
	)
		
	BEGIN TRANSACTION
			INSERT INTO #tmpPromotions
			(PromotionId, RatePerPush, Lat, Lon)
			SELECT PromotionPushQueue.PromotionId, PromotionPushQueue.RatePerPush, PromotionPushQueue.Latitude, PromotionPushQueue.Longitude
			FROM PromotionPushQueue (NOLOCK) left join Promotion (nolock) on PromotionPushQueue.PromotionId = Promotion.PromotionId
											left join Business (nolock) on Promotion.BusinessId = Business.BusinessId
											left join ProximityRadius (nolock) on Promotion.ProximityRadiusId = ProximityRadius.ProximityRadiusId
			WHERE (PromotionPushQueue.UserId = @UserId)
				AND (
						((Promotion.ProximityRadiusId not in (12, 13)) AND (dbo.fnCalculateDistance(@Latitude, @Longitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, ProximityRadius.ToRadiusFeet / 5280)))
											-- over 200 miles
											OR ((Promotion.ProximityRadiusId = 13) AND (dbo.fnCalculateDistance(@Latitude, @Longitude, Business.Latitude, Business.Longitude) > CONVERT(FLOAT, ProximityRadius.FromRadiusFeet / 5280)))
											-- custom range
											OR ((Promotion.ProximityRadiusId = 12) AND (dbo.fnCalculateDistance(@Latitude, @Longitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, Promotion.CustomRange)))
					)
			
			if @@error <> 0 return
				
			SELECT @Result = (SELECT Promotion.PromotionId, Promotion.PromotionText, Business.BusinessId, Business.BusinessName,
								STR(dbo.fnCalculateDistance(@Latitude, @Longitude, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
								vwBusinessRating.RatingText AS Rating,
								vwBusinessRating.CountReviews AS Reviews,
								15 AS PriceRange,
								Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
								Business.Latitude, Business.Longitude,
								Promotion.PromotionText, Business.[Description], 
								
								Business.BusinessPhone, Business.CellPhone, 
								Business.TollFreePhone, Business.Web,
								
								(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name
									FROM BusinessCategory (NOLOCK) LEFT JOIN Category (NOLOCK) ON BusinessCategory.CategoryId = Category.CategoryId
									WHERE BusinessCategory.BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS Categories,
								(SELECT [WeekDay] AS Name, 
									dbo.fnAPI_Format_Time(FromTime) AS Start, 
									dbo.fnAPI_Format_Time(ToTime) AS [End], 
									Closed
								FROM BusinessHours AS [Day] (NOLOCK)
								WHERE [Day].BusinessId = Business.BusinessId
								FOR XML AUTO, TYPE) AS BusinessHours
							FROM #tmpPromotions pp (nolock) INNER JOIN Promotion (NOLOCK) ON pp.PromotionId = Promotion.PromotionId
											LEFT JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
											LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
													--WHERE pp.UserId = @UserId
							FOR XML PATH ('Promotion'))
							
			INSERT INTO PromotionPush
			(PromotionId, UserId, DatePush, Latitude, Longitude, RatePerPush)
			SELECT PromotionId, @UserId, GETUTCDATE(), Lat, Lon, RatePerPush
			FROM #tmpPromotions
			--WHERE (NOT EXISTS(SELECT 1 FROM PromotionPush WHERE (PromotionPush.UserId = @UserId) AND (PromotionPush.PromotionId = #tmpPromotions.PromotionId)))
			
			if @@error <> 0 return
			
			DELETE
			FROM PromotionPushQueue
			WHERE (UserId = @UserId)
				AND (PromotionId IN (SELECT PromotionId FROM #tmpPromotions))

			if @@error <> 0 return
			
			update #tmpPromotions
			set WillStop = 1
			from Promotion LEFT JOIN (SELECT PromotionId, SUM(RatePerPush) AS UsedBudget
											FROM PromotionPush
											GROUP BY PromotionId) AS vwPush ON Promotion.PromotionId = vwPush.PromotionId
							LEFT JOIN (SELECT PromotionId, SUM(FixedCost) AS Amount
											FROM PromotionCost
											GROUP BY PromotionId) AS vwPromotionCost ON Promotion.PromotionId = vwPromotionCost.PromotionId
			where (Promotion.PromotionId = #tmpPromotions.PromotionId)
				AND (Promotion.CampaignBudget - ISNULL(vwPush.UsedBudget, 0) - ISNULL(vwPromotionCost.Amount, 0) < Promotion.RatePerPush)
						
			/*UPDATE Promotion
			SET StatusCode = 'COM'
			FROM #tmpPromotions LEFT JOIN (SELECT PromotionId, SUM(RatePerPush) AS UsedBudget
											FROM PromotionPush
											GROUP BY PromotionId) AS vwPush ON #tmpPromotions.PromotionId = vwPush.PromotionId
								LEFT JOIN (SELECT PromotionId, SUM(FixedCost) AS Amount
											FROM PromotionCost
											GROUP BY PromotionId) AS vwPromotionCost ON #tmpPromotions.PromotionId = vwPromotionCost.PromotionId
			WHERE (Promotion.PromotionId = #tmpPromotions.PromotionId)
				AND (Promotion.CampaignBudget - ISNULL(vwPush.UsedBudget, 0) - ISNULL(vwPromotionCost.Amount, 0) < Promotion.RatePerPush)*/
				
			update Promotion
			set StatusCode = 'COM', DateCompleted = GETDATE()
			from #tmpPromotions				
			where (Promotion.PromotionId = #tmpPromotions.PromotionId)
				and (#tmpPromotions.WillStop = 1)
				
				
			-- Calc rest and save rest
			declare cursorStopped cursor for 
			select PromotionId
			from #tmpPromotions
			where WillStop = 1
			
			open cursorStopped
			fetch from cursorStopped into @promotionIdCurr
			
			while @@FETCH_STATUS = 0
			begin
				exec spPromotionRest_Add @PromotionId = @promotionIdCurr
				fetch from cursorStopped into @promotionIdCurr
			end
			
			close cursorStopped
			deallocate cursorStopped
			
			if @@error <> 0 return
			
	COMMIT TRANSACTION
		
	DROP TABLE #tmpPromotions
END
go

ALTER PROCEDURE [dbo].[spPromotion_StartStop]
(
	@PromotionId INT = NULL
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @Now DATETIME
	SET @Now = GETUTCDATE()
	
	declare @promotionIdCurr int
	
	CREATE TABLE #tmpPromotion1
	(
		PromotionId INT,
		Gmt INT
	)
	
	CREATE TABLE #tmpPromotion2
	(
		PromotionId INT,
		Gmt INT,
		WillStop bit default(0)
	)
	
	IF @PromotionId IS NOT NULL
		INSERT INTO #tmpPromotion1
		(PromotionId, Gmt)
		SELECT Promotion.PromotionId, Business.Gmt
		FROM Promotion (NOLOCK) INNER JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
		WHERE (Promotion.PromotionId = @PromotionId)
			AND (Promotion.StatusCode = 'PEN')
			AND (Promotion.Deleted = 0)
			AND (Promotion.Ready = 1)
			AND (Promotion.BaseCostTimingId IS NOT NULL)
			AND (
				-- 2, 4 - starts now
				(Promotion.BaseCostTimingId IN (2, 4)) OR 
				-- 3 - shedule at
				((Promotion.BaseCostTimingId = 3) AND (Promotion.BaseCostTimingScheduledAt <= @Now)) OR
				-- 5 - shedule date range
				((Promotion.BaseCostTimingId = 5) AND (Promotion.BaseCostTimingStarts <= @Now))
				)
	ELSE
		INSERT INTO #tmpPromotion1
		(PromotionId, Gmt)
		SELECT Promotion.PromotionId, Business.Gmt
		FROM Promotion (NOLOCK) INNER JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
		WHERE (Promotion.StatusCode = 'PEN')
			AND (Promotion.Deleted = 0)
			AND (Promotion.Ready = 1)
			AND (Promotion.BaseCostTimingId IS NOT NULL)
			AND (
				-- 2, 4 - starts now
				(Promotion.BaseCostTimingId IN (2, 4)) OR 
				-- 3 - shedule at
				((Promotion.BaseCostTimingId = 3) AND (Promotion.BaseCostTimingScheduledAt <= @Now)) OR
				-- 5 - shedule date range
				((Promotion.BaseCostTimingId = 5) AND (Promotion.BaseCostTimingStarts <= @Now))
				)
	
	-- Starts promotion
	UPDATE Promotion
	SET StatusCode = 'ACT'
	FROM #tmpPromotion1
	WHERE (Promotion.PromotionId = #tmpPromotion1.PromotionId)

	-- Make pay for starts promotion
	INSERT INTO PromotionCost
	(PromotionId, DateSubmit, FixedCost)
	SELECT Promotion.PromotionId, GETUTCDATE(), Promotion.FixedCost
	FROM Promotion (NOLOCK) INNER JOIN #tmpPromotion1 ON Promotion.PromotionId = #tmpPromotion1.PromotionId
					
	IF @PromotionId IS NOT NULL
		INSERT INTO #tmpPromotion2
		(PromotionId, Gmt)
		SELECT Promotion.PromotionId, Business.Gmt
		FROM Promotion (NOLOCK) INNER JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
		WHERE (PromotionId = @PromotionId)
			AND (Promotion.StatusCode = 'ACT')
			AND (Promotion.Deleted = 0)
			AND (Promotion.Ready = 1)
			AND (Promotion.BaseCostTimingId IS NOT NULL)
	ELSE
		INSERT INTO #tmpPromotion2
		(PromotionId, Gmt)
		SELECT Promotion.PromotionId, Business.Gmt
		FROM Promotion (NOLOCK) INNER JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
		WHERE (Promotion.StatusCode = 'ACT')
			AND (Promotion.Deleted = 0)
			AND (Promotion.Ready = 1)
			AND (Promotion.BaseCostTimingId IS NOT NULL)
			
	-- Mark promo for stop
	update #tmpPromotion2
	set WillStop = 1
	from Promotion
	where (Promotion.PromotionId = #tmpPromotion2.PromotionId)
		AND (Promotion.StatusCode = 'ACT')
		AND (Promotion.BaseCostTimingId = 5) 
		AND ((Promotion.BaseCostTimingEnds <= @Now) or (DATEDIFF(DAY, Promotion.BaseCostTimingStarts, GETDATE()) > 7))
		
	update #tmpPromotion2
	set WillStop = 1
	from Promotion
	WHERE (Promotion.PromotionId = #tmpPromotion2.PromotionId)
		AND (Promotion.StatusCode = 'ACT')
		AND (Promotion.BaseCostTimingId  IN (2, 3, 4)) 
		AND (DATEDIFF(DAY, Promotion.BaseCostTimingScheduledAt, GETDATE()) > 7)
	
	/*-- Stop promotion
	UPDATE Promotion
	SET StatusCode = 'COM'			
	FROM #tmpPromotion2
	WHERE (Promotion.PromotionId = #tmpPromotion2.PromotionId)
		AND (Promotion.StatusCode = 'ACT')
		AND (Promotion.BaseCostTimingId = 5) 
		AND ((Promotion.BaseCostTimingEnds <= @Now) or (DATEDIFF(DAY, Promotion.BaseCostTimingStarts, GETDATE()) > 7))

	UPDATE Promotion
	SET StatusCode = 'COM'			
	FROM #tmpPromotion2
	WHERE (Promotion.PromotionId = #tmpPromotion2.PromotionId)
		AND (Promotion.StatusCode = 'ACT')
		AND (Promotion.BaseCostTimingId  IN (2, 3, 4)) 
		AND (DATEDIFF(DAY, Promotion.BaseCostTimingScheduledAt, GETDATE()) > 7)*/
		
	-- Stop promotion
	update Promotion
	set StatusCode = 'COM', DateCompleted = GETDATE()			
	from #tmpPromotion2
	where (Promotion.PromotionId = #tmpPromotion2.PromotionId)
		and (#tmpPromotion2.WillStop = 1) 
		
	-- Calc rest and save rest
	declare cursorStopped cursor for 
	select PromotionId
	from #tmpPromotion2
	where WillStop = 1
	
	open cursorStopped
	fetch from cursorStopped into @promotionIdCurr
	
	while @@FETCH_STATUS = 0
	begin
		exec spPromotionRest_Add @PromotionId = @promotionIdCurr
		fetch from cursorStopped into @promotionIdCurr
	end
	
	close cursorStopped
	deallocate cursorStopped

	DROP TABLE #tmpPromotion1
	DROP TABLE #tmpPromotion2
END
go

ALTER PROCEDURE [dbo].[spAPI_Promotion_Stop]
(
	@Token VARCHAR(255),
	@PromotionId INT
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @CampaignBudget MONEY
	DECLARE @CreditCardNo VARCHAR(20)	
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
		IF EXISTS(SELECT 1 FROM Promotion (NOLOCK) WHERE BusinessId = @BusinessId AND PromotionId = @PromotionId)
		BEGIN
			UPDATE Promotion
			SET StatusCode = 'COM', DateCompleted = GETDATE()
			WHERE (PromotionId = @PromotionId)
			
			exec spPromotionRest_Add @PromotionId = @PromotionId
		END	
		ELSE
			SET @IdResult = -1

	SELECT @IdResult AS IdResult
END
go

ALTER FUNCTION [dbo].[fnTransaction_List]
(
  @BusinessId INT,
  @PromotionId INT
)
RETURNS @retList TABLE
(
	RowId INT IDENTITY(1, 1),
	PromotionId INT,
	DateOperation DATETIME,
	TypeOperation VARCHAR(50),
	Amount MONEY,
	TransactionId INT,
	SortFlag INT,
	Balance MONEY
)	
AS  
BEGIN
	INSERT INTO @retList
	(PromotionId, DateOperation, TypeOperation, Amount, TransactionId, SortFlag)
		
	/*SELECT PromotionPush.PromotionId, 
		DATEADD(DD, (DATEDIFF(DD, 0, PromotionPush.DatePush)), 0) AS DateOperation, 
		'Charge', -SUM(PromotionPush.RatePerPush), -1 AS TransactionId, 2 AS SortFlag
	FROM Promotion (NOLOCK) INNER JOIN PromotionPush (NOLOCK) ON Promotion.PromotionId = PromotionPush.PromotionId
	WHERE (Promotion.BusinessId = @BusinessId)
		AND (Promotion.PromotionId = @PromotionId OR @PromotionId IS NULL)
	GROUP BY PromotionPush.PromotionId, DATEADD(DD, (DATEDIFF(DD, 0, PromotionPush.DatePush)), 0)
		
	UNION ALL
	
	SELECT PromotionCost.PromotionId, 
		DATEADD(DD, (DATEDIFF(DD, 0, PromotionCost.DateSubmit)), 0) AS DateOperation, 
		'FlatFee', -PromotionCost.FixedCost, PromotionCost.PromotionCostId AS TransactionId, 1 AS SortFlag
	FROM Promotion (NOLOCK) INNER JOIN PromotionCost (NOLOCK) ON Promotion.PromotionId = PromotionCost.PromotionId
	WHERE (Promotion.BusinessId = @BusinessId)
		AND (Promotion.PromotionId = @PromotionId OR @PromotionId IS NULL)
				
	UNION ALL
	
	SELECT PromotionPay.PromotionId, 
		DATEADD(DD, (DATEDIFF(DD, 0, PromotionPay.DatePay)), 0) AS DateOperation, 
		'CCard', PromotionPay.Amount, PromotionPay.PromotionPayId AS TransactionId, 0 AS SortFlag
	FROM Promotion (NOLOCK) INNER JOIN PromotionPay (NOLOCK) ON Promotion.PromotionId = PromotionPay.PromotionId
	WHERE (Promotion.BusinessId = @BusinessId)
		AND (Promotion.PromotionId = @PromotionId OR @PromotionId IS NULL)
		and (PromotionPay.CreditCardNo is not null)
		
	ORDER BY PromotionId, DateOperation, SortFlag, TransactionId*/
	
	/*select PromotionRest.PromotionId, PromotionRest.DateRest as DateOperation, 'Rest', PromotionRest.Amount, PromotionRest.PromotionRestId as TransactionId, 1 as SortFlag
	from PromotionRest (nolock) left join Promotion (nolock) on PromotionRest.PromotionId = Promotion.PromotionId
	where (Promotion.BusinessId = @BusinessId)
		and (Promotion.PromotionId = @PromotionId or @PromotionId is null)
		
	union all
	
	select PromotionPay.PromotionId, PromotionPay.DatePay as DateOperation, 'Charge', -PromotionPay.Amount, PromotionPay.PromotionPayId as TransactionId, 2 as SortFlag
	from PromotionPay (nolock) inner join Promotion (nolock) on PromotionPay.PromotionId = Promotion.PromotionId
	where (Promotion.BusinessId = @BusinessId)
		and (Promotion.PromotionId = @PromotionId or @PromotionId is null)
		and (PromotionPay.CreditCardNo is null)
		
	order by DateOperation, SortFlag, TransactionId*/
	
	SELECT Promotion.PromotionId, 
		DATEADD(DD, (DATEDIFF(DD, 0, isnull(Promotion.DateCompleted, Promotion.DateAdded))), 0) AS DateOperation, 
		'Fees', -ISNULL(SUM(PromotionPush.RatePerPush), 0) - isnull(PromotionCost.FixedCost, 0), -1 AS TransactionId, 2 AS SortFlag
	FROM Promotion (NOLOCK) LEFT JOIN PromotionPush (NOLOCK) ON Promotion.PromotionId = PromotionPush.PromotionId
							INNER JOIN PromotionCost (NOLOCK) ON Promotion.PromotionId = PromotionCost.PromotionId
	WHERE (Promotion.BusinessId = @BusinessId)
		AND (Promotion.PromotionId = @PromotionId OR @PromotionId IS NULL)
		and (Promotion.StatusCode = 'COM')
	GROUP BY Promotion.PromotionId, DATEADD(DD, (DATEDIFF(DD, 0, isnull(Promotion.DateCompleted, Promotion.DateAdded))), 0), PromotionCost.FixedCost
		
	UNION ALL
	
	SELECT PromotionPay.PromotionId, 
		DATEADD(DD, (DATEDIFF(DD, 0, PromotionPay.DatePay)), 0) AS DateOperation, 
		'CC Charge', PromotionPay.Amount, PromotionPay.PromotionPayId AS TransactionId, 0 AS SortFlag
	FROM Promotion (NOLOCK) INNER JOIN PromotionPay (NOLOCK) ON Promotion.PromotionId = PromotionPay.PromotionId
	WHERE (Promotion.BusinessId = @BusinessId)
		AND (Promotion.PromotionId = @PromotionId OR @PromotionId IS NULL)
		and (PromotionPay.CreditCardNo is not null)
		
	order by DateOperation, SortFlag, TransactionId
			
	DECLARE @RowId INT, @PromotionIdLocal INT, @PromotionIdPrev INT, @Amount MONEY, @RunningTotal MONEY
	SET @PromotionIdPrev = 0
		
	DECLARE cursorOperations 
	CURSOR FOR 
	SELECT RowId, Amount, PromotionId
	FROM @retList
	ORDER BY RowId 
		
	OPEN cursorOperations
	FETCH FROM cursorOperations INTO @RowId, @Amount, @PromotionIdLocal
		
	SET @RunningTotal = 0
	WHILE @@FETCH_STATUS = 0
	BEGIN
		/*IF @PromotionIdLocal <> @PromotionIdPrev
			SET @RunningTotal = 0*/
				
		SET @RunningTotal = @RunningTotal + @Amount
		SET @PromotionIdPrev = @PromotionIdLocal
			
		UPDATE @retList 
		SET Balance = @RunningTotal 
		WHERE RowId = @RowId
			
		FETCH FROM cursorOperations INTO @RowId, @Amount, @PromotionIdLocal
	END
		
	CLOSE cursorOperations
	DEALLOCATE cursorOperations
	
	RETURN
END
go

ALTER PROCEDURE [dbo].[spAPI_Billing_Transaction_History]
(
	@Token VARCHAR(255),
	@PromotionId INT = NULL,
	@returnAsDataset bit = 0,
	@DateOperation datetime = null
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @Result XML
	
	DECLARE @BusinessId INT
	DECLARE @Balance MONEY
		
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
	BEGIN
		CREATE TABLE #tmpOperations
		(
			RowId INT,
			PromotionId INT,
			DateOperation DATETIME,
			TypeOperation VARCHAR(50),
			Amount MONEY,
			TransactionId INT,
			SortFlag INT,
			Balance MONEY
		)
		
		INSERT INTO #tmpOperations
		(RowId, PromotionId, DateOperation, TypeOperation, Amount, TransactionId, SortFlag, Balance)
		SELECT RowId, PromotionId, DateOperation, TypeOperation, Amount, TransactionId, SortFlag, Balance
		FROM dbo.fnTransaction_List(@BusinessId, @PromotionId)
								
		select @Balance = SUM(amount)
		from #tmpOperations
		set @Balance = ISNULL(@Balance, 0)
		
		if (@DateOperation is not null)
			delete from #tmpOperations where DateOperation <> @DateOperation
				
		if @returnAsDataset = 0
		begin
			SET @Result = (SELECT @Balance AS CurrentAccoutnBalance,
								(SELECT
									#tmpOperations.TransactionId
									, #tmpOperations.DateOperation AS [Date]
									, #tmpOperations.TypeOperation AS [Type]
									, case when Promotion.PromotionNumber < 10 then '0' else '' end
										+ cast(Promotion.PromotionNumber as varchar) AS [Description]
									, /*Promotion.Name as PromotionName*/ 'Promo ' + cast(Promotion.PromotionNumber as varchar) as PromotionName
									, CASE WHEN #tmpOperations.TypeOperation = 'Fees' THEN -#tmpOperations.Amount ELSE 0 END AS Debit
									, CASE WHEN #tmpOperations.TypeOperation = 'CC Charge' THEN #tmpOperations.Amount ELSE 0 END AS Credit
									, #tmpOperations.Balance
									, #tmpOperations.PromotionId
								FROM #tmpOperations LEFT JOIN Promotion ON #tmpOperations.PromotionId = Promotion.PromotionId
								ORDER BY #tmpOperations.DateOperation DESC, #tmpOperations.SortFlag DESC
								FOR XML PATH ('Transaction'), ROOT('Transactions'), TYPE)
							FOR XML PATH ('TransactionHistory'))
		end
		else
		begin
			SELECT
				@Balance AS CurrentAccoutnBalance,
				#tmpOperations.TransactionId
				, #tmpOperations.DateOperation AS [Date]
				, #tmpOperations.TypeOperation AS [Type]
				, case when Promotion.PromotionNumber < 10 then '0' else '' end
					+ cast(Promotion.PromotionNumber as varchar) AS [Description]
				, /*Promotion.Name as PromotionName*/ 'Promo ' + cast(Promotion.PromotionNumber as varchar) as PromotionName
				, CASE WHEN #tmpOperations.TypeOperation = 'Fees' THEN -#tmpOperations.Amount ELSE 0 END AS Debit
				, CASE WHEN #tmpOperations.TypeOperation = 'CC Charge' THEN #tmpOperations.Amount ELSE 0 END AS Credit
				, #tmpOperations.Balance
				, #tmpOperations.PromotionId
			FROM #tmpOperations LEFT JOIN Promotion ON #tmpOperations.PromotionId = Promotion.PromotionId
			ORDER BY #tmpOperations.DateOperation DESC, #tmpOperations.SortFlag DESC
			
			return
		end
	END

	if @returnAsDataset = 0		
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result 
END
go
ALTER PROCEDURE [dbo].[spBusiness_GetBilling]
	@BusinessId INT,
	@DateOperation datetime = null
AS
BEGIN
	SET NOCOUNT ON

	CREATE TABLE #tmpOperations
	(
		RowId INT,
		PromotionId INT,
		DateOperation DATETIME,
		TypeOperation VARCHAR(50),
		Amount MONEY,
		TransactionId INT,
		SortFlag INT,
		Balance MONEY
	)
		
	INSERT INTO #tmpOperations
	(RowId, PromotionId, DateOperation, TypeOperation, Amount, TransactionId, SortFlag, Balance)
	SELECT RowId, PromotionId, DateOperation, TypeOperation, Amount, TransactionId, SortFlag, Balance
	FROM dbo.fnTransaction_List(@BusinessId, NULL)
	
	if (@DateOperation is not null)
		delete from #tmpOperations where DateOperation <> @DateOperation
	
	SELECT #tmpOperations.DateOperation AS [Date],
		#tmpOperations.TypeOperation AS [Type],
		'Promo ' + CASE WHEN Promotion.PromotionNumber < 10 THEN '0' ELSE '' END + 
		CAST(Promotion.PromotionNumber AS VARCHAR) AS [Description],
		CASE WHEN #tmpOperations.TypeOperation = 'Fees' THEN -#tmpOperations.Amount ELSE 0 END AS Debit,
		CASE WHEN #tmpOperations.TypeOperation = 'CC Charge' THEN #tmpOperations.Amount ELSE 0 END AS Credit,
		#tmpOperations.Balance, '' AS Comments
	FROM #tmpOperations LEFT JOIN Promotion ON #tmpOperations.PromotionId = Promotion.PromotionId
	ORDER BY #tmpOperations.DateOperation DESC, #tmpOperations.SortFlag DESC
	
	DROP TABLE #tmpOperations
END
go




